﻿@using System.Configuration
@using System.Data.SqlClient
@{
    Layout = "~/_SiteLayout.cshtml";
    Page.Title = "Sync Approval Page";

    bool hasError = false;
    string errorMessage = string.Empty;
    bool hasSuccess = false;
    string successMessage = string.Empty;

    System.Data.DataSet ds = new System.Data.DataSet("CurrentClaims");
    System.Data.DataTable dtB2E = null;
    
    
    if (IsPost)
    {
        if (Request.Form["actionButton"] == "update")
        {
            using (System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection("Data Source=kldb;Initial Catalog=B2EPilot;User ID=b2epilotwebacct;Password=p@55w0rd;Application Name=Staff-Overtime;Workstation ID=AN2594"))
            {
                System.Data.SqlClient.SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText =
@"SELECT  a.EmployeeMaster, 
        NULLIF(LTRIM(RTRIM(c.LeaveRec1)), '') AS 'RecommenderId1', 
        NULLIF(LTRIM(RTRIM(c.LeaveRec2)), '') AS 'RecommenderId2', 
        NULLIF(LTRIM(RTRIM(c.LeaveApp1)), '') AS 'ApproverId1', 
        NULLIF(LTRIM(RTRIM(c.LeaveApp2)), '') AS 'ApproverId2'
FROM    tblDataActivation a 
INNER JOIN 
        tblDataPersonnel b 
        ON a.EmployeeMaster = b.EmployeeMaster 
INNER JOIN 
        tblDataPersonnelB2E c 
        ON b.EmployeeMaster = c.EmployeeMaster
WHERE   b.DateOfTermination IS NULL 
ORDER BY a.Section, c.AccessLevel, b.NameLegal;";

                System.Data.SqlClient.SqlDataAdapter da = new System.Data.SqlClient.SqlDataAdapter(cmd);
                da.Fill(ds, "B2E");

                if ((ds != null) && (ds.Tables.Count > 0) && (ds.Tables["B2E"].Rows.Count > 0))
                {
                    dtB2E = ds.Tables["B2E"];
                }
            }

            if (dtB2E != null)
            {
                using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["EOCS"].ConnectionString))
                {
                    System.Data.SqlClient.SqlCommand cmd = conn.CreateCommand();
                    

                    conn.Open();
                    using (System.Data.SqlClient.SqlTransaction sqlTran = conn.BeginTransaction())
                    {
                        cmd.CommandText = "DELETE FROM ReportingStructure";
                        cmd.Transaction = sqlTran;
                        cmd.ExecuteNonQuery();
                        
                        using (System.Data.SqlClient.SqlBulkCopy bcp = new System.Data.SqlClient.SqlBulkCopy(conn, System.Data.SqlClient.SqlBulkCopyOptions.Default, sqlTran))
                        {
                            bcp.DestinationTableName = "ReportingStructure";
                            bcp.WriteToServer(dtB2E);

                            hasSuccess = true;
                            successMessage = @"<span class=""glyphicon glyphicon-ok"" aria-hidden=""true""></span> Sync complete.";
                        }
                        
                        sqlTran.Commit();
                    }
                }
            }
            
        } // if (Request.Form["actionButton"] == "submit")
            
    } // if (IsPost)
    
}

<div class="container-fluid main-content" role="main">

    <h2>
        Sync Approval
    </h2>
    
    <div class="panel panel-default">
        <div class="panel-heading">Instructions (How should this page work?)</div>
        <div class="panel-body">
            <p>
                This page is for users to sync B2E approval.<br />
            </p>

            <ul>
                <li>HR can click on Sync B2E button update records.</li>
            </ul>
        </div>
    </div>

    @if (hasError)
    {
    <div class="alert alert-danger alert-dismissible" role="alert">
        <button type="button" class="close" data-dismiss="alert" aria-label="Close"><span aria-hidden="true">&times;</span></button>
        <strong>Warning!</strong> @errorMessage
    </div>
    }
    @if (hasSuccess)
    {
    <div class="alert alert-success alert-dismissible" role="alert">
        <button type="button" class="close" data-dismiss="alert" aria-label="Close"><span aria-hidden="true">&times;</span></button>
        <strong>Success!</strong> @Html.Raw(successMessage)
    </div>
    }

    <form method="post">

        <button type="submit" name="actionButton" value="update" class="btn btn-primary">
            <span class="glyphicon glyphicon-refresh" aria-hidden="true"></span>
            Sync Approval
        </button>

        @Html.Raw(successMessage)
        
    </form>

</div>

@section Scripts
{
    <script type="text/javascript" src="~/Scripts/kendo/2014.3.1119/kendo.ui.core.min.js"></script>
    <script type="text/javascript" src="~/Scripts/moment.js"></script>
    <script type="text/javascript" src="~/Scripts/moment-with-locales.js"></script>
    <script type="text/javascript">
    (function ($) {
        "use strict";

        $(document).ready(function () {

        });

    }(jQuery));

    </script>
}